package db;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.LinkedList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import server.ServerConstants;
import simulation.SimulationConstants;

import entities.Location;
import entities.Message;

public class MySQLDAO extends MessageDAO {
	private final String dbUrl = "jdbc:mysql://" + ServerConstants.IP_ADDRESS + ":" + ServerConstants.MYSQL_PORT + "/CS4440?user=root&password=masterofdisguise";
	private final String dbClass = "com.mysql.jdbc.Driver";

	private PreparedStatement psInsertMessageRecipient;
	
	private final int COL_ID = 1;
	private final int COL_SENDER_ID = 2;
	private final int COL_LOCATION = 3;
	private final int COL_TIME_OF_ORIGIN = 4;
	private final int COL_TIME_OF_EXPIRY = 5;
	private final int COL_MESSAGE = 6;

	private Connection connection;

	public static MessageDAO getInstance() {
		if (instance == null) {
			instance = new MySQLDAO();
		}
		return instance;
	}

	private MySQLDAO() {
		connect();
		try {
			psInsertMessageRecipient = connection.prepareStatement("INSERT INTO MessageRecipient (Message_id, recipientId) VALUES (?, ?)");
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void dbConnect() {
		try {
			Class.forName(dbClass);
			connection = DriverManager.getConnection(dbUrl);
		}
		catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void dbCloseConnection() {
		try {
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public long time() { return System.currentTimeMillis(); }
	
	@Override
	public void dbAddMessage(Message message) {
		//long t0 = time();
		try {
			String location = String.format("GeomFromText('Point(%f %f)')", message.getLocation().getLatitude(), message.getLocation().getLongitude());
			PreparedStatement ps = connection.prepareStatement(
					"INSERT INTO Message(senderId, location, timeOfOrigin, timeOfExpiry, message) " +
					"VALUES (?, " + location + ", ?, ?, ?)");
			// System.out.println("\n\n ADD MESSAGE");
			//System.out.println("T1: " + (time() - t0));
			ps.setInt(1, message.getSenderId());
			ps.setTimestamp(2, new Timestamp(message.getTimeOfOrigin().getTime()));
			ps.setTimestamp(3, new Timestamp(message.getTimeOfExpiry().getTime()));
			ps.setString(4, message.getMessage());
			//System.out.println("T2: " + (time() - t0));
			ps.execute();
			//System.out.println("T3: " + (time() - t0));
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public List<Message> dbGetMessagesForRecipientLocation(int recipientId,
			Location currLoc) {
		List<Message> messages = new LinkedList<Message>();
		//long t0 = time();
		try {
			String locationCondition = String.format("distance(location, GeomFromText('Point(%f %f)')) < %f", currLoc.getLatitude(), currLoc.getLongitude(), SimulationConstants.MESSAGE_RANGE);
			PreparedStatement psGetMessagesForRecipientLocation = connection.prepareStatement(
					"SELECT id, senderId, AsText(location), timeOfOrigin, timeOfExpiry, message " +
					"FROM Message " +
					"WHERE id NOT IN " +
					"(" +
						"SELECT Message_id " +
						"FROM MessageRecipient " +
						"WHERE recipientId = ?" +
					") AND " +
					locationCondition
			);
			//System.out.println("\n\nGET MESSAGES");
			//System.out.println("T1: " + (time() - t0));
			psGetMessagesForRecipientLocation.setInt(1, recipientId);
			ResultSet rs = psGetMessagesForRecipientLocation.executeQuery();
			//System.out.println("T2: " + (time() - t0));
			while (rs.next()) {
				messages.add(getMessageFromResultSet(rs));
			}
			//System.out.println("T3: " + (time() - t0));
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			for (Message message : messages) {
				psInsertMessageRecipient.setInt(1, message.getId());
				psInsertMessageRecipient.setInt(2, recipientId);
				psInsertMessageRecipient.execute();
			}
			//System.out.println("T4: " + (time() - t0));
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		return messages;
	}
	
	/*
	CREATE FUNCTION distance (a POINT, b POINT) RETURNS double
	DETERMINISTIC
	COMMENT 'distance function'
	BEGIN
	 RETURN glength(linestring(a, b));
	END
	*/
	
	public List<Message> dbGetAllMessages() {
		List<Message> allMessages = new LinkedList<Message>();

		try {
			Statement stmt = connection.createStatement();
			String query = "" +
					"SELECT id, senderId, AsText(location), timeOfOrigin, timeOfExpiry, message " +
					"FROM Message";
			ResultSet rs = stmt.executeQuery(query);

			while (rs.next()) {				
				allMessages.add(getMessageFromResultSet(rs));
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		return allMessages;
	}
	
	private Message getMessageFromResultSet(ResultSet rs) {
		Message message = null;
		try {
			message = new Message(
				rs.getInt(COL_ID),
				rs.getInt(COL_SENDER_ID),
				pointTextToLocation(rs.getString(COL_LOCATION)),
				new Date(rs.getTimestamp(COL_TIME_OF_ORIGIN).getTime()),
				new Date(rs.getTimestamp(COL_TIME_OF_EXPIRY).getTime()),
				rs.getString(COL_MESSAGE)
			);
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		return message;
	}
	
	private Location pointTextToLocation(String locationText) {
		String pointRegEx = "POINT\\((.+) (.+)\\)";
		Matcher matcher = Pattern.compile(pointRegEx).matcher(locationText);
		if (matcher.find()) {
			double latitude = Double.parseDouble(matcher.group(1));
			double longitude = Double.parseDouble(matcher.group(2));
			return new Location(latitude, longitude);
		}
		return null;
	}

	@Override
	protected void dbClearDatabase() {
		try {
			PreparedStatement ps = connection.prepareStatement("DELETE FROM MessageRecipient");
			ps.execute();
			ps = connection.prepareStatement("DELETE FROM Message");
			ps.execute();
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}
}